package com.nicedistractions.roadtriptracker;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class DBHelper {

	public static final String KEY_ROWID = "_id";
	public static final String KEY_LAT = "latitude";
	public static final String KEY_LONG = "longitude";
	public static final String KEY_ACC = "accuracy";	
	public static final String KEY_ALT = "altitude";
	public static final String KEY_BEAR = "bearing";
	public static final String KEY_SPEED = "speed";
	public static final String KEY_TIME = "time";
	public static final String KEY_UPLOADED = "uploaded";

	private static final String TAG = "DBHelper";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE_LOCATIONS = "create table locations ("
			+ "_id integer primary key autoincrement, "
			+ "latitude double not null, "
			+ "longitude double not null, "
			+ "accuracy float not null, "
			+ "altitude double not null, "
			+ "bearing float not null, "
			+ "speed float not null, "
			+ "time long not null, "
			+ "uploaded long default 0);";

	private static final String DATABASE_NAME = "roadtriptracker";
	private static final String DATABASE_LOCATIONS_TABLE = "locations";
	
	//////////////////////////////////////////////
	private static final int DATABASE_VERSION = 2;
	//////////////////////////////////////////////

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i("DB", "Creating tables");
			db.execSQL(DATABASE_CREATE_LOCATIONS);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion);   
			
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_LOCATIONS_TABLE);
			onCreate(db);
		}
	}
	

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx	the Context within which to work
	 */
	public DBHelper(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this 			(self reference, allowing this to be chained in an Initialisation call)
	 * @throws SQLException		if the database could be neither opened or created
	 */
	public DBHelper open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long addLocation(double lat, double longitude, float acc, double alt, float bearing, float speed, long time) {
		ContentValues locationValues = new ContentValues();

		locationValues.put(KEY_LAT, lat);
		locationValues.put(KEY_LONG, longitude);
		locationValues.put(KEY_ACC, acc);
		locationValues.put(KEY_ALT, alt);
		locationValues.put(KEY_BEAR, bearing);
		locationValues.put(KEY_SPEED, speed);
		locationValues.put(KEY_TIME, time);
		
		// Make a new entry in food table
		return mDb.insert(DATABASE_LOCATIONS_TABLE, null, locationValues);
	}
	
	/**
	 * Updates an item stored in the DB.
	 * 
	 * @param foodId
	 * @param productId
	 * @param name
	 * @param useby
	 * @param bcode
	 * @param frozen
	 * @param useByDays
	 * @return
	 */
	public boolean updateItem(long locationId, double lat, double longitude, float acc, double alt, float bearing, float speed, long time, boolean uploaded) {
		ContentValues locationValues = new ContentValues();
		
		locationValues.put(KEY_LAT, lat);
		locationValues.put(KEY_LONG, longitude);
		locationValues.put(KEY_ACC, acc);
		locationValues.put(KEY_ALT, alt);
		locationValues.put(KEY_BEAR, bearing);
		locationValues.put(KEY_SPEED, speed);
		locationValues.put(KEY_TIME, time);
		locationValues.put(KEY_UPLOADED, uploaded);		
		
		return mDb.update(DATABASE_LOCATIONS_TABLE, locationValues, KEY_ROWID + "="	+ locationId, null) > 0;
	}
	
	public Cursor getProduct(long locationId) {
		Cursor mCursor = mDb.query(true, DATABASE_LOCATIONS_TABLE, null, KEY_ROWID + "=" + locationId, null, null, null, null, null);
		mCursor.moveToFirst();
		return mCursor;
	}

	public boolean deleteLocation(long locationId) {
		return mDb.delete(DATABASE_LOCATIONS_TABLE, KEY_ROWID + "=" + locationId, null) > 0;
	}

	public Cursor fetchAllItems() {
		return mDb.query(DATABASE_LOCATIONS_TABLE, new String[] { KEY_ROWID, KEY_LAT, KEY_LONG, KEY_ACC, KEY_ALT, KEY_BEAR, KEY_SPEED, KEY_TIME }, null, null, null, null, KEY_TIME);
	}	
	
	public Cursor getLocationsToUpload() {
		return mDb.query(true, DATABASE_LOCATIONS_TABLE, new String[] { KEY_ROWID, KEY_LAT, KEY_LONG, KEY_ACC, KEY_ALT, KEY_BEAR, KEY_SPEED, KEY_TIME }, KEY_UPLOADED + "=" + 0, null, null, null, null, null);
	}
	
	public long locationUploaded(long id, long uploaded) {
		ContentValues productValues = new ContentValues();
		productValues.put(KEY_UPLOADED, uploaded);

		return mDb.update(DATABASE_LOCATIONS_TABLE, productValues, KEY_ROWID + "=" + id, null);		 
	}
}